﻿using System;
using System.Collections.Generic;
using System.Text;
using DataBaseReverseGenerator.DataBaseModel;
using System.Data.OleDb;
using System.Data;

namespace DataBaseReverseGenerator.DataBaseModelGenerator
{
    public class DataBaseExplorer
    {
        #region Constants

        protected const string COL_TABLE_SCHEMA = "TABLE_SCHEMA";
        protected const string COL_TABLE_NAME = "TABLE_NAME";
        protected const string COL_TABLE_TYPE = "TABLE_TYPE";
        protected const string TABLE_TYPE_TABLE = "TABLE";

        protected const string COL_COLUMN_NAME = "COLUMN_NAME";
        
        protected const string COL_PK_TABLE_NAME = "PK_TABLE_NAME";
        protected const string COL_PK_COLUMN_NAME = "PK_COLUMN_NAME";
        protected const string COL_FK_TABLE_NAME = "FK_TABLE_NAME";
        protected const string COL_FK_COLUMN_NAME = "FK_COLUMN_NAME";
        protected const string COL_FK_ORDINAL = "ORDINAL";
        
        #endregion

        #region Members

        protected OleDbConnection dbConn;

        #endregion

        #region Ctor

        public DataBaseExplorer()
        {
        }

        public DataBaseExplorer(string connStr)
        {
            this.connectionString = connStr;
        }

        #endregion

        #region Properties

        private string connectionString;
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        private DataBase dbModel;
        public DataBase DbModel
        {
            get { return dbModel; }
            set { dbModel = value; }
        }

        #endregion
        
        #region Methods

        public virtual void Explore()
        {
            using (dbConn = new OleDbConnection(this.connectionString))
            {
                try
                {
                    dbConn.Open();
                    this.DbModel = new DataBase(dbConn.Database);

                    ExploreTables();
                    ExploreRelationShips();
                }
                finally
                {
                    dbConn.Close();
                }
            }
        }


        protected virtual void ExploreTables()
        {
            DataTable dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, null });

            foreach (DataRow dr in dt.Rows)
            {
                if (((string)dr[COL_TABLE_TYPE]) == TABLE_TYPE_TABLE)
                {
                    string tableName = (string)dr[COL_TABLE_NAME];
                    Table t = new Table(tableName);
                    t.Schema = dr[COL_TABLE_SCHEMA] == DBNull.Value ? string.Empty : (string)dr[COL_TABLE_SCHEMA];
                    this.DbModel.Tables.Add(t);

                    ExploreColumns(tableName);
                    ExplorePrimaryKeys(tableName);
                }
            }
        }


        protected virtual void ExploreColumns(string tableName)
        {
            Table table = this.DbModel.Tables.Find(delegate(Table t) { return t.Name == tableName; });

            // SSX: DataType not strictly identified 
            #region 
            //if (table != null)
            //{
            //    DataTable dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });

            //    foreach (DataRow dr in dt.Rows)
            //    {
            //        string colName = (string)dr[COL_COLUMN_NAME];
            //        Column c = new Column(colName);
            //        c.Size = (int)dr[COL_COLUMN_DATATYPE];
            //        table.Columns.Add(c);
            //    }
            //}
            #endregion

            if (table != null)
            {
                string tableSchema = string.Empty;
                if(table.Schema != string.Empty)
                {
                    tableSchema = String.Format("[{0}].", table.Schema);
                }

                System.Data.Common.DbCommand cmd = new OleDbCommand(String.Format("SELECT * FROM {0}[{1}]", tableSchema, tableName), dbConn);
                using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                {
                    DataTable dt = reader.GetSchemaTable();

                    foreach (DataRow dr in dt.Rows)
                    {
                        string colName = (string)dr[System.Data.Common.SchemaTableColumn.ColumnName];
                        Column c = new Column(colName);
                        c.Type = (OleDbType)dr[System.Data.Common.SchemaTableColumn.ProviderType];
                        c.Size = (int)dr[System.Data.Common.SchemaTableColumn.ColumnSize];
                        c.IsIdentity = (bool)dr[System.Data.Common.SchemaTableOptionalColumn.IsReadOnly] && c.Type == OleDbType.Integer;

                        table.Columns.Add(c);
                    }
                }
            }

        }

        protected virtual void ExplorePrimaryKeys(string tableName)
        {
            Table table = this.DbModel.Tables.Find(delegate(Table t) { return t.Name == tableName; });

            if (table != null)
            {
                DataTable dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new object[] { null, null, tableName });

                foreach (DataRow dr in dt.Rows)
                {
                    string colName = (string)dr[COL_COLUMN_NAME];
                    Column pk = table.Columns.Find(delegate(Column c) { return c.Name == colName; });

                    table.PrimaryKeys.Add(pk.Name);
                }
            }
        }


        protected virtual void ExploreRelationShips()
        {
            DataTable dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null });

            Dictionary<string, int> relationNumber = new Dictionary<string, int>();

            foreach (DataRow dr in dt.Rows)
            {
                string parentTable = (string)dr[COL_PK_TABLE_NAME];
                string parentField = (string)dr[COL_PK_COLUMN_NAME];
                string childTable = (string)dr[COL_FK_TABLE_NAME];
                string childField = (string)dr[COL_FK_COLUMN_NAME];
                long ordinal = (long)dr[COL_FK_ORDINAL];

                string relationName = String.Concat(parentTable, "_", childTable);

                RelationShip rel = this.DbModel.RelationShips.Find(delegate(RelationShip r) { return r.Name == relationName; });
                if (rel == null)
                {
                    // new relationship
                    rel = new RelationShip(relationName);
                    rel.ParentTable = parentTable;
                    rel.ChildTable = childTable;

                    this.DbModel.RelationShips.Add(rel);
                    relationNumber.Add(relationName, 0);
                }
                else
                {
                    // another relationship between same tables
                    if (ordinal == 1)
                    {
                        int relationNb = ++relationNumber[relationName];
                        relationName = String.Concat(parentTable, "_", childTable, "_", relationNb);

                        rel = new RelationShip(relationName);
                        rel.ParentTable = parentTable;
                        rel.ChildTable = childTable;

                        this.DbModel.RelationShips.Add(rel);
                    }
                    // otherwise new rule for an existing relationship
                }

                rel.Rules.Add(new DataBaseReverseGenerator.DataBaseModel.Rule(parentField, childField));
            }
        }

        #region For Debugging

        private static void DisplayDataTable(DataTable dtSchema)
        {
            foreach (DataColumn col in dtSchema.Columns)
            {
                Console.Write(col.Caption + "\t");
            }
            Console.WriteLine();

            foreach (DataRow dr in dtSchema.Rows)
            {
                for (int i = 0; i < dtSchema.Columns.Count; i++)
                {
                    Console.Write(dr[i] + "\t");
                }
                Console.WriteLine();
            }
        }

        #endregion

        #endregion
    }
}
